


/**
 * 
 */
package bookshop.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import bookshop.dao.ManagerDao;
import bookshop.domain.Book;
import bookshop.domain.Manager;
import bookshop.exception.DataAccessException;
import bookshop.service.helper.ManagerQueryHelper;
import bookshop.utils.DBUtils;

/**
 * @author Administrator
 *
 */
public class ManagerDaoJDBCImpl implements ManagerDao {
	
	private static final String SQL_GET_BYID="select * from manager where login_id=?";
	private static final String SQL_ADD = "insert into manager(login_id,login_pwd,realname,status) values(?,?,?,?)";
	private static final String SQL_LOADALL = "select * from manager order by login_id desc";
	private static final String SQL_UPDATE="update manager set login_pwd=?,realname=? where login_id=?";
	private static final String SQL_UP_STATUS="update manager set status=? where login_id=?";
	private static final String SQL_DEL="delete from manager where login_id=?";
	
	
	private String generateSQL(ManagerQueryHelper helper){
		
		 String sql="select * from manager where 1=1";
		 
		 if(helper.getQryManagerId()!=null)
			 sql+=" and login_id like '%"+helper.getQryManagerId()+"%' ";
		 if(helper.getQryManagerName()!=null)
			 sql+=" and realname like '%"+helper.getQryManagerName()+"%' ";	 
		 
		 
		 sql+=" order by login_id ";
		
          return sql;		
	}


	/* (non-Javadoc)
	 * @see edu.fzu.bookshop.dao.ManagerDao#getManagerById(java.lang.String)
	 */
	public Manager getManagerById(String loginId) {
		
		Connection conn=DBUtils.getInstance().getConn();
		PreparedStatement pstmt=null;
		ResultSet rset=null;
		Manager manager=null;
		
		try {
			
			pstmt=conn.prepareStatement(SQL_GET_BYID);
			pstmt.setString(1, loginId);
			rset=pstmt.executeQuery();
			
			if(rset.next()){
			   manager=new Manager();
			   manager.setLoginId(rset.getString("login_id"));
			   manager.setLoginPwd(rset.getString("login_pwd"));
			   manager.setRealName(rset.getString("realname"));
			   manager.setStatus(rset.getString("status"));
			}
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			DBUtils.getInstance().releaseRes(conn, pstmt, rset);
		}
		
		return manager;
	}



public void addManager(Manager manager) {

	Connection ct = DBUtils.getInstance().getConn();
	PreparedStatement ps = null;

	try {
		ps = ct.prepareStatement(SQL_ADD);
		ps.setString(1, manager.getLoginId());
		ps.setString(2, manager.getLoginPwd());
		ps.setString(3, manager.getRealName());
		ps.setString(4, "Y");
		ps.executeUpdate();
	} catch (SQLException e) {
		e.printStackTrace();
		throw new DataAccessException("管理员信息保存失败，可能管理员出现重复，请检查数据!");
	} finally {
		DBUtils.getInstance().releaseRes(ct, ps, null);
	}

}
public List<Manager> loadall() {
	Connection ct = DBUtils.getInstance().getConn();
	PreparedStatement ps = null;
	ResultSet rs = null;
	List<Manager> managers = null;
	try {
		ps = ct.prepareStatement(SQL_LOADALL);
		rs = ps.executeQuery();
		while (rs.next()) {
			if (managers == null)
				managers = new ArrayList<Manager>();
			Manager manager = new Manager();

			manager.setLoginId(rs.getString("login_id"));
			manager.setLoginPwd(rs.getString("login_pwd"));
			manager.setRealName(rs.getString("realname"));
			  String status=rs.getString("status");
              if(status==null||status.equals("Y"))  status="正常使用";
              if(status.equals("N"))  status="冻结停用";
              manager.setStatus(status);
              managers.add(manager);

		}
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.getInstance().releaseRes(ct, ps, rs);
	}

	return managers;

}




@Override
public void update(Manager manager) {
	Connection ct=DBUtils.getInstance().getConn();
	PreparedStatement ps=null;
	
	
	try {
		ps=ct.prepareStatement(SQL_UPDATE);
		ps.setString(3, manager.getLoginId());
		ps.setString(1, manager.getLoginPwd());
		ps.setString(2, manager.getRealName());
		ps.executeUpdate();
		
	} catch (SQLException e) {
		e.printStackTrace();
	}finally{
		DBUtils.getInstance().releaseRes(ct, ps, null);
	}
}



@Override
public void delete(String loginId) {
	Connection ct=DBUtils.getInstance().getConn();
	PreparedStatement ps=null;
	
	
	try {
		ps=ct.prepareStatement(SQL_DEL);
		ps.setString(1, loginId);
		ps.executeUpdate();
	} catch (SQLException e) {
		e.printStackTrace();
	}finally{
		DBUtils.getInstance().releaseRes(ct, ps, null);
	}
	
	
}



@Override
public void up_status(Manager manager) {
	Connection ct=DBUtils.getInstance().getConn();
	PreparedStatement ps=null;
	
	
	try {
		ps=ct.prepareStatement(SQL_UP_STATUS);
		ps.setString(2, manager.getLoginId());
		ps.setString(1, manager.getStatus());
		ps.executeUpdate();
		
	} catch (SQLException e) {
		e.printStackTrace();
	}finally{
		DBUtils.getInstance().releaseRes(ct, ps, null);
	}
	
}



@Override
public List<Manager> loadManagerByCondition(ManagerQueryHelper helper) {
	String sql=this.generateSQL(helper);
	System.out.println(sql);
	Connection ct = DBUtils.getInstance().getConn();
	PreparedStatement ps = null;
	ResultSet rs = null;
	List<Manager> managers = null;
	try {
		ps = ct.prepareStatement(sql);
		rs = ps.executeQuery();
		while (rs.next()) {
			if (managers == null)
				managers = new ArrayList<Manager>();
			Manager manager = new Manager();

			manager.setLoginId(rs.getString("login_id"));
			manager.setLoginPwd(rs.getString("login_pwd"));
			manager.setRealName(rs.getString("realname"));
			  String status=rs.getString("status");
              if(status==null||status.equals("Y"))  status="正常使用";
              if(status.equals("N"))  status="冻结停用";
              manager.setStatus(status);
              managers.add(manager);

		}
	} catch (SQLException e) {
		e.printStackTrace();
		throw new DataAccessException("管理员查询列表获取失败!");
	} finally {
		DBUtils.getInstance().releaseRes(ct, ps, rs);
	}
	
	return managers;
}


@Override
public Long countManagerByCondition(ManagerQueryHelper helper) {
	
	String sql=this.generateSQL(helper);
	sql=sql.replace("*","count(*) as total");
	sql=sql.substring(0,sql.indexOf("order by"));
	Connection conn=DBUtils.getInstance().getConn();
	PreparedStatement ps=null;
	ResultSet rs=null;
	Long count =0L;
	try {
		ps=conn.prepareStatement(sql);
		rs=ps.executeQuery();
		if(rs.next())
		{
			
			count=rs.getLong("total");
			
		} 
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}finally{
		DBUtils.getInstance().releaseRes(conn, ps, rs);
 }
	
	return count;
	
}


@Override
public List<Manager> getScopedManager(ManagerQueryHelper helper,
		int startIndex, int fetchSize) {
	String sql = this.generateSQL(helper);
	sql += " limit " +startIndex+ ","+fetchSize;
	System.out.println("countManagerByCondition:"+sql);

	
	Connection ct = DBUtils.getInstance().getConn();
	PreparedStatement ps = null;
	ResultSet rs = null;
	List<Manager> managers = null;
	try {
		ps = ct.prepareStatement(sql);
		rs = ps.executeQuery();
		while (rs.next()) {
			if (managers == null)
				managers = new ArrayList<Manager>();
			Manager manager = new Manager();

			manager.setLoginId(rs.getString("login_id"));
			manager.setLoginPwd(rs.getString("login_pwd"));
			manager.setRealName(rs.getString("realname"));
			  String status=rs.getString("status");
              if(status==null||status.equals("Y"))  status="正常使用";
              if(status.equals("N"))  status="冻结停用";
              manager.setStatus(status);
              managers.add(manager);

		}
	} catch (SQLException e) {
		e.printStackTrace();
		throw new DataAccessException("管理员分页查询列表获取失败!");
	} finally {
		DBUtils.getInstance().releaseRes(ct, ps, rs);
	}
	
	return managers;
}

}






